import pandas as pd
import mysql.connector
from datetime import datetime

# 数据库配置
SOURCE_DB_CONFIG = {
    'host': '172.24.222.53',
    'port': 3306,
    'user': 'report',
    'password': 'WjC#Ch&!WTUP9',
    'database': 'asd_lmdm_prd'
}

def read_excel_data():
    """读取Excel文件数据"""
    excel_path = "/Users/qingjunluo/Desktop/服务商库存-后台清理2025-2-21.xlsx"
    try:
        df = pd.read_excel(excel_path)
        return df
    except Exception as e:
        print(f"读取Excel文件失败: {str(e)}")
        return None

def get_brand_codes(conn, brand_names):
    """根据品牌名称查询品牌代码"""
    cursor = conn.cursor(dictionary=True)
    brand_codes = {}
    
    try:
        for brand_name in brand_names:
            query = """
            SELECT 
                ebcd.ebcd_code as brand_code,
                ebcd.ebcd_name_cn as brand_name
            FROM 
                eb_code_master ebcd 
            WHERE 1=1
                and ebcd.ebcd_name_cn LIKE %s
                and ebcd.ebcd_code <>'0002171485'
                AND ebcd.ebcd_dictionary_type = 'material_product_type'
            """
            cursor.execute(query, (f'%{brand_name}%',))
            result = cursor.fetchone()
            if result:
                brand_codes[brand_name] = result['brand_code']
            else:
                print(f"未找到品牌 '{brand_name}' 的代码")
            cursor.fetchall()  # 确保读取所有结果
    finally:
        cursor.close()
    
    return brand_codes

def get_inventory_data(conn, brand_code, clear_date):
    """查询服务商库存数据"""
    cursor = conn.cursor(dictionary=True)
    try:
        query = """
        SELECT 
            cdwh.CDWH_CODE, 
            cdwh.CDWH_NAME, 
            wil.LOT_NUM, 
            wil.SKU_CODE, 
            wil.OWNER_CODE, 
            wil.QTY, 
            wil.LOC_CODE,
            wila.LOT_ATT04, 
            wila.LOT_ATT08, 
            wila.LOT_ATT03
        FROM wm_inv_lot_loc wil
        JOIN wm_inv_lot_att wila 
            ON (wil.PROJECT_ID = wila.PROJECT_ID AND wil.LOT_NUM = wila.LOT_NUM)
        JOIN eb_project ebpj 
            ON (wil.PROJECT_ID = EBPJ_ID)
        JOIN cd_warehouse cdwh 
            ON (ebpj.ebpj_wh_code = cdwh.CDWH_CODE AND cdwh.cdwh_yc_type = 'EN')
        WHERE wil.OWNER_CODE = %s
            AND wila.LOT_ATT03 < %s
        """
        
        cursor.execute(query, (brand_code, clear_date))
        results = cursor.fetchall()
        return results
    finally:
        cursor.close()

def convert_clear_date(date_str):
    """转换清理日期格式"""
    if not isinstance(date_str, str) or date_str in ['不清', '暂不清']:
        return None
    
    try:
        # 处理类似 "2024年10月份" 的格式
        if '-' in date_str:
            year = int(date_str.split("-")[0])
            month =int(date_str.split("-")[1])
            # 获取该月最后一天
            if month == 12:
                return f"{year}-12-31"
            else:
                next_month = datetime(year, month + 1, 1)
                return next_month
        if '全清' in date_str:
            return '2099-12-31'
    except Exception as e:
        print(f"日期转换错误: {date_str}, {str(e)}")
        return None
    return None

def main():
    # 读取Excel数据
    df = read_excel_data()
    if df is None:
        return
    
    try:
        # 连接数据库
        conn = mysql.connector.connect(**SOURCE_DB_CONFIG)
        brand_names = df['品牌名称'].unique()
        brand_codes = get_brand_codes(conn, brand_names)
        all_results = pd.DataFrame()
        
        for index, row in df.iterrows():
            brand_name = row['品牌名称']
            clear_date = convert_clear_date(row['清理到月份'])
            print(f"\n品牌: {brand_name} - 清理日期: {clear_date}")
            
            # 如果日期转换失败或是"不清"类型，跳过该记录
            if not clear_date:
                print(f"\n品牌: {brand_name} - 跳过（清理日期: {row['清理到月份']}）")
                continue
            
            if brand_name not in brand_codes:
                continue
                
            brand_code = brand_codes[brand_name]
            inventory_data = get_inventory_data(conn, brand_code, clear_date)
            
            if inventory_data:
                current_df = pd.DataFrame(inventory_data)
                # 添加额外的信息列
                current_df['品牌名称'] = brand_name
                current_df['清理到月份'] = clear_date
                current_df['查询时间'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                
                # 重命名列名为中文
                current_df = current_df.rename(columns={
                    'CDWH_CODE': '仓库代码',
                    'CDWH_NAME': '仓库名称',
                    'LOT_NUM': '批次号',
                    'LOC_CODE': '库位信息',
                    'SKU_CODE': '商品编码',
                    'OWNER_CODE': '货主代码',
                    'QTY': '数量',
                    'LOT_ATT04': '品质',
                    'LOT_ATT08': 'SN代码',
                    'LOT_ATT03': '入库日期'
                })
                
                # 设置列的显示顺序
                columns_order = [
                    '品牌名称', '仓库代码', '仓库名称', '商品编码', 
                    '批次号','库位信息', '数量', '入库日期', '品质', 'SN代码',
                    '货主代码', '清理到月份', '查询时间'
                ]
                current_df = current_df[columns_order]
                
                all_results = pd.concat([all_results, current_df], ignore_index=True)
            
            print(f"\n品牌: {brand_name} (代码: {brand_code})")
            print(f"清理截止日期: {clear_date}")
            print(f"找到 {len(inventory_data)} 条库存记录")
        
        # 保存所有结果到Excel文件
        if not all_results.empty:
            output_filename = f"/Users/qingjunluo/Desktop/库存查询结果_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
            
            # 创建一个ExcelWriter对象
            with pd.ExcelWriter(output_filename, engine='xlsxwriter') as writer:
                # 写入数据
                all_results.to_excel(writer, sheet_name='库存明细', index=False)
                
                # 获取workbook和worksheet对象
                workbook = writer.book
                worksheet = writer.sheets['库存明细']
                
                # 设置列宽
                worksheet.set_column('A:L', 15)  # 设置所有列宽为15
                worksheet.set_column('B:C', 20)  # 仓库代码和名称列宽设为20
                worksheet.set_column('E:E', 20)  # 批次号列宽设为20
                
                # 创建格式
                header_format = workbook.add_format({
                    'bold': True,
                    'align': 'center',
                    'valign': 'vcenter',
                    'fg_color': '#D9D9D9',
                    'border': 1
                })
                
                # 应用表头格式
                for col_num, value in enumerate(columns_order):
                    worksheet.write(0, col_num, value, header_format)
            
            print(f"\n所有结果已保存到: {output_filename}")
    
    except Exception as e:
        print(f"处理过程中出错: {str(e)}")
    
    finally:
        if 'conn' in locals():
            conn.close()

if __name__ == "__main__":
    main()